package edu.xpu.project.analysis.service;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import edu.xpu.project.analysis.model.ClassAvgModel;
import edu.xpu.project.analysis.sql.ScoreAnalysisSql;

/**
 * @describe 单科成绩分析业务逻辑类
 * @author liukang
 * @date 2017年5月28日
 */
@Service
public class SingleScoreAnalysisService {
	
	@Autowired
	private EntityManagerFactory factory;
	
	public Integer getScoreTotalByCauseIdAndClassIdForLT(Long couseId, Long classId, Integer score) {
		EntityManager entityManager = factory.createEntityManager();
		Query query = entityManager.createNativeQuery(ScoreAnalysisSql.getStudentNumByCourseIdAndClassIdLTscore(couseId, classId, score));
		Object singleResult = query.getSingleResult();
		BigInteger result = (BigInteger) singleResult;
		entityManager.close();
		return result.intValue();
	}
	
	public Integer getScoreTotalByCauseIdAndClassIdForGT(Long couseId, Long classId, Integer score) {
		EntityManager entityManager = factory.createEntityManager();
		Query query = entityManager.createNativeQuery(ScoreAnalysisSql.getStudentNumByCourseIdAndClassIdGTscore(couseId, classId, score));
		Object singleResult = query.getSingleResult();
		BigInteger result = (BigInteger) singleResult;
		entityManager.close();
		return result.intValue();
	}
	
	public Integer getScoreTotalByCauseIdAndClassId(Long couseId, Long classId) {
		EntityManager entityManager = factory.createEntityManager();
		Query query = entityManager.createNativeQuery(ScoreAnalysisSql.getStudentNumByCourseIdAndClassId(couseId, classId));
		Object singleResult = query.getSingleResult();
		BigInteger result = (BigInteger) singleResult;
		entityManager.close();
		return result.intValue();
	}
	
	/**
	 * 根据班级和科目获取top N成绩
	 * @param n
	 * @param classId
	 * @param courseId
	 * @return
	 */
	@Transactional(propagation=Propagation.NOT_SUPPORTED)
	public List<Object> getScoreListByCourseIdAndClassid(Integer n, Long classId, Long courseId) {
		EntityManager entityManager = factory.createEntityManager();
		//EntityTransaction transaction = entityManager.getTransaction();
		//开启事务
		//transaction.begin();
		Query query = entityManager.createNativeQuery(ScoreAnalysisSql.getSingleTopNByClassIdAndCourseid(n, courseId, classId));
		@SuppressWarnings("unchecked")
		List<Object> objList = query.getResultList();
		//提交事务
		//transaction.commit();
		entityManager.close();
		return objList;
	}
	
	/**
	 * @describe 获取每个班的均分
	 * @date 2017年5月29日
	 * @author liukang
	 */
	@SuppressWarnings("unchecked")
	@Transactional(propagation=Propagation.NOT_SUPPORTED)
	public List<ClassAvgModel> getAvgByClasses(Long courseId) {
		EntityManager entityManager = factory.createEntityManager();
		//EntityTransaction transaction = entityManager.getTransaction();
		//开启事务
		//transaction.begin();
		Query query = entityManager.createNativeQuery(ScoreAnalysisSql.getSingleAvgSql(courseId));
		List<Object[]> resultList = query.getResultList();
		//进行构造ClassAvgModel对象
		List<ClassAvgModel> avgModels = new ArrayList<ClassAvgModel>(resultList.size());
		for (Object[] objects : resultList) {
			if (objects.length == 4) {
				ClassAvgModel classAvgModel = new ClassAvgModel();
				classAvgModel.setClassId(((BigInteger)(objects[0])).longValue());
				classAvgModel.setClassAvg(((Double)objects[1]));
				classAvgModel.setClassName((String)objects[2]);
				classAvgModel.setGradeName((String)objects[3]);
				avgModels.add(classAvgModel);
			}
		}
		//提交事务
		//transaction.commit();
		entityManager.close();
		return avgModels;
	}


}
